// 库存管理路由
const express = require('express');
const router = express.Router();
const { query } = require('../config/database');
const { authenticateToken, requireAdmin } = require('../middleware/auth');
const { validateId, validatePagination } = require('../middleware/validation');
const { calculatePagination, formatAmount } = require('../utils/helpers');
const ResponseHelper = require('../utils/response');

// 获取库存列表
router.get('/', authenticateToken, requireAdmin, validatePagination, async (req, res) => {
  try {
    const { page, pageSize, offset, limit } = calculatePagination(req.query.page, req.query.pageSize);
    const { category, status, search, low_stock } = req.query;

    // 构建查询条件
    let whereClause = 'WHERE 1=1';
    let queryParams = [];

    if (category) {
      whereClause += ' AND category = ?';
      queryParams.push(category);
    }

    if (status) {
      whereClause += ' AND status = ?';
      queryParams.push(status);
    }

    if (search) {
      whereClause += ' AND (name LIKE ? OR description LIKE ? OR sku LIKE ?)';
      const searchPattern = `%${search}%`;
      queryParams.push(searchPattern, searchPattern, searchPattern);
    }

    if (low_stock === 'true') {
      whereClause += ' AND current_stock <= min_stock';
    }

    // 获取库存总数
    const countResult = await query(
      `SELECT COUNT(*) as total FROM inventory ${whereClause}`,
      queryParams
    );
    const total = countResult[0].total;

    // 获取库存列表
    const inventory = await query(
      `SELECT id, name, sku, category, current_stock, min_stock, max_stock, 
              unit_price, total_value, unit, status, description, 
              created_at, updated_at 
       FROM inventory ${whereClause} 
       ORDER BY name 
       LIMIT ? OFFSET ?`,
      [...queryParams, limit, offset]
    );

    ResponseHelper.paginated(res, inventory, total, page, pageSize, '获取库存列表成功');

  } catch (error) {
    console.error('获取库存列表错误:', error);
    ResponseHelper.serverError(res, '获取库存列表失败');
  }
});

// 获取库存统计
router.get('/stats', authenticateToken, requireAdmin, async (req, res) => {
  try {
    // 获取库存基本统计
    const [inventoryStats] = await query(`
      SELECT 
        COUNT(*) as total_items,
        SUM(current_stock) as total_stock,
        SUM(total_value) as total_value,
        SUM(CASE WHEN current_stock <= min_stock THEN 1 ELSE 0 END) as low_stock_items,
        SUM(CASE WHEN current_stock = 0 THEN 1 ELSE 0 END) as out_of_stock_items,
        SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) as active_items
      FROM inventory
    `);

    // 获取分类统计
    const categoryStats = await query(`
      SELECT 
        category,
        COUNT(*) as item_count,
        SUM(current_stock) as total_stock,
        SUM(total_value) as total_value
      FROM inventory
      GROUP BY category
      ORDER BY total_value DESC
    `);

    // 获取库存状态分布
    const statusStats = await query(`
      SELECT 
        status,
        COUNT(*) as count
      FROM inventory
      GROUP BY status
    `);

    ResponseHelper.success(res, {
      summary: {
        ...inventoryStats,
        total_value: formatAmount(inventoryStats.total_value)
      },
      category_distribution: categoryStats,
      status_distribution: statusStats
    }, '获取库存统计成功');

  } catch (error) {
    console.error('获取库存统计错误:', error);
    ResponseHelper.serverError(res, '获取库存统计失败');
  }
});

// 获取库存分类
router.get('/categories', authenticateToken, requireAdmin, async (req, res) => {
  try {
    const categories = await query(`
      SELECT 
        category,
        COUNT(*) as item_count
      FROM inventory
      GROUP BY category
      ORDER BY category
    `);

    ResponseHelper.success(res, categories, '获取库存分类成功');

  } catch (error) {
    console.error('获取库存分类错误:', error);
    ResponseHelper.serverError(res, '获取库存分类失败');
  }
});

// 获取单个库存项
router.get('/:id', authenticateToken, requireAdmin, validateId, async (req, res) => {
  try {
    const inventoryId = req.params.id;

    const inventory = await query(
      `SELECT id, name, sku, category, current_stock, min_stock, max_stock, 
              unit_price, total_value, unit, status, description, 
              created_at, updated_at 
       FROM inventory WHERE id = ?`,
      [inventoryId]
    );

    if (inventory.length === 0) {
      return ResponseHelper.notFound(res, '库存项不存在');
    }

    ResponseHelper.success(res, inventory[0], '获取库存项成功');

  } catch (error) {
    console.error('获取库存项错误:', error);
    ResponseHelper.serverError(res, '获取库存项失败');
  }
});

// 创建库存项
router.post('/', authenticateToken, requireAdmin, async (req, res) => {
  try {
    const {
      name, sku, category, current_stock, min_stock, max_stock,
      unit_price, unit, status = 'active', description
    } = req.body;

    // 验证必填字段
    if (!name || !sku || !category || current_stock === undefined || !unit_price || !unit) {
      return ResponseHelper.error(res, '请填写所有必填字段', 400);
    }

    // 检查SKU是否已存在
    const existingSku = await query('SELECT id FROM inventory WHERE sku = ?', [sku]);
    if (existingSku.length > 0) {
      return ResponseHelper.error(res, 'SKU已存在', 400);
    }

    // 计算总价值
    const totalValue = parseFloat(current_stock) * parseFloat(unit_price);

    // 创建库存项
    const result = await query(
      `INSERT INTO inventory (
        name, sku, category, current_stock, min_stock, max_stock,
        unit_price, total_value, unit, status, description, created_at, updated_at
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())`,
      [
        name, sku, category, current_stock, min_stock || 0, max_stock || 999999,
        unit_price, totalValue, unit, status, description || ''
      ]
    );

    // 获取创建的库存项
    const newInventory = await query(
      `SELECT id, name, sku, category, current_stock, min_stock, max_stock, 
              unit_price, total_value, unit, status, description, 
              created_at, updated_at 
       FROM inventory WHERE id = ?`,
      [result.insertId]
    );

    ResponseHelper.success(res, newInventory[0], '库存项创建成功', 201);

  } catch (error) {
    console.error('创建库存项错误:', error);
    ResponseHelper.serverError(res, '创建库存项失败');
  }
});

// 更新库存项
router.put('/:id', authenticateToken, requireAdmin, validateId, async (req, res) => {
  try {
    const inventoryId = req.params.id;
    const {
      name, sku, category, current_stock, min_stock, max_stock,
      unit_price, unit, status, description
    } = req.body;

    // 检查库存项是否存在
    const existingInventory = await query('SELECT id FROM inventory WHERE id = ?', [inventoryId]);
    if (existingInventory.length === 0) {
      return ResponseHelper.notFound(res, '库存项不存在');
    }

    // 检查SKU是否已被其他库存项使用
    if (sku) {
      const duplicateSku = await query(
        'SELECT id FROM inventory WHERE sku = ? AND id != ?',
        [sku, inventoryId]
      );

      if (duplicateSku.length > 0) {
        return ResponseHelper.error(res, 'SKU已被其他库存项使用', 400);
      }
    }

    // 计算总价值
    let totalValue = null;
    if (current_stock !== undefined && unit_price !== undefined) {
      totalValue = parseFloat(current_stock) * parseFloat(unit_price);
    }

    // 处理 undefined 值，转换为 null
    const params = [
      name !== undefined ? name : null,
      sku !== undefined ? sku : null,
      category !== undefined ? category : null,
      current_stock !== undefined ? current_stock : null,
      min_stock !== undefined ? min_stock : null,
      max_stock !== undefined ? max_stock : null,
      unit_price !== undefined ? unit_price : null,
      totalValue !== undefined ? totalValue : null,
      unit !== undefined ? unit : null,
      status !== undefined ? status : null,
      description !== undefined ? description : null,
      inventoryId
    ];

    // 更新库存项
    await query(
      `UPDATE inventory SET
       name = COALESCE(?, name),
       sku = COALESCE(?, sku),
       category = COALESCE(?, category),
       current_stock = COALESCE(?, current_stock),
       min_stock = COALESCE(?, min_stock),
       max_stock = COALESCE(?, max_stock),
       unit_price = COALESCE(?, unit_price),
       total_value = COALESCE(?, total_value),
       unit = COALESCE(?, unit),
       status = COALESCE(?, status),
       description = COALESCE(?, description),
       updated_at = NOW()
       WHERE id = ?`,
      params
    );

    // 获取更新后的库存项
    const updatedInventory = await query(
      `SELECT id, name, sku, category, current_stock, min_stock, max_stock, 
              unit_price, total_value, unit, status, description, 
              created_at, updated_at 
       FROM inventory WHERE id = ?`,
      [inventoryId]
    );

    ResponseHelper.success(res, updatedInventory[0], '库存项更新成功');

  } catch (error) {
    console.error('更新库存项错误:', error);
    ResponseHelper.serverError(res, '更新库存项失败');
  }
});

// 更新库存数量
router.patch('/:id/stock', authenticateToken, requireAdmin, validateId, async (req, res) => {
  try {
    const inventoryId = req.params.id;
    const { quantity, operation, reason } = req.body; // operation: 'add' | 'subtract' | 'set'

    if (!quantity || quantity <= 0) {
      return ResponseHelper.error(res, '数量必须大于0', 400);
    }

    if (!['add', 'subtract', 'set'].includes(operation)) {
      return ResponseHelper.error(res, '操作类型无效', 400);
    }

    // 获取当前库存信息
    const inventory = await query(
      'SELECT id, name, current_stock, unit_price FROM inventory WHERE id = ?',
      [inventoryId]
    );

    if (inventory.length === 0) {
      return ResponseHelper.notFound(res, '库存项不存在');
    }

    const currentItem = inventory[0];
    const currentStock = parseFloat(currentItem.current_stock);
    let newStock;

    switch (operation) {
      case 'add':
        newStock = currentStock + parseFloat(quantity);
        break;
      case 'subtract':
        newStock = Math.max(0, currentStock - parseFloat(quantity));
        break;
      case 'set':
        newStock = parseFloat(quantity);
        break;
    }

    // 计算新的总价值
    const newTotalValue = newStock * parseFloat(currentItem.unit_price);

    // 更新库存
    await query(
      'UPDATE inventory SET current_stock = ?, total_value = ?, updated_at = NOW() WHERE id = ?',
      [newStock, newTotalValue, inventoryId]
    );

    // 记录库存变动日志（这里可以扩展为单独的库存变动记录表）
    console.log(`库存变动: ${currentItem.name} - ${operation} ${quantity}, 原库存: ${currentStock}, 新库存: ${newStock}, 原因: ${reason || '无'}`);

    ResponseHelper.success(res, {
      inventory_id: inventoryId,
      old_stock: currentStock,
      new_stock: newStock,
      operation,
      quantity: parseFloat(quantity),
      new_total_value: formatAmount(newTotalValue)
    }, '库存数量更新成功');

  } catch (error) {
    console.error('更新库存数量错误:', error);
    ResponseHelper.serverError(res, '更新库存数量失败');
  }
});

// 删除库存项
router.delete('/:id', authenticateToken, requireAdmin, validateId, async (req, res) => {
  try {
    const inventoryId = req.params.id;

    // 检查库存项是否存在
    const inventory = await query('SELECT id, name FROM inventory WHERE id = ?', [inventoryId]);
    if (inventory.length === 0) {
      return ResponseHelper.notFound(res, '库存项不存在');
    }

    // 删除库存项
    await query('DELETE FROM inventory WHERE id = ?', [inventoryId]);

    ResponseHelper.success(res, null, '库存项删除成功');

  } catch (error) {
    console.error('删除库存项错误:', error);
    ResponseHelper.serverError(res, '删除库存项失败');
  }
});

module.exports = router;
